/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package br.com.dao;

import br.com.classes.Historico;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author Eziquiel
 */
public class HistoricoDao extends Conecta {

    Connection con = null;

    public HistoricoDao() {
        con = Conecta.getConnection();
    }

    public void CadastrarHistorico(Historico h) throws Exception {

        String sql = ("insert into historico values(null,?)");
        PreparedStatement prep = con.prepareStatement(sql);
        prep.setString(1, h.getHistorico());
        prep.execute();
    }

    public void DeletarHistorico(Historico h) throws Exception {

        String sql = ("delete from historico where historico = ?");
        PreparedStatement prep = con.prepareStatement(sql);
        prep.setString(1, h.getHistorico());
        prep.execute();
    }

    public void AlterarHistorico(Historico h) {

        try {
            String sql = "update historico set historico=? where cod_historico=?";
            PreparedStatement ps = con.prepareStatement(sql);

            ps.setString(1, h.getHistorico());
            ps.setInt(2, h.getCod_historico());
            ps.execute();

        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
    }

    public Historico PesquisarHistorico(int cod_historico) {

        Historico h = null;
        try {
            String sql = "select * from historico where cod_historico=?";
            PreparedStatement ps = con.prepareStatement(sql);
            ps.setInt(1, cod_historico);
            ResultSet rs = ps.executeQuery();

            while (rs.next()) {
                h = new Historico();
                h.setHistorico(rs.getString("historico"));
            }
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
        return h;
    }

    public List<Historico> ListarHistoricos() {

        List<Historico> lista = new ArrayList<Historico>();

        try {
            String sql = "select * from historico where cod_historico > 1 order by cod_historico desc";
            PreparedStatement stmt = con.prepareStatement(sql);
            ResultSet rs = stmt.executeQuery();

            while (rs.next()) {
                Historico his = new Historico();
                his.setCod_historico(rs.getInt("cod_historico"));
                his.setHistorico(rs.getString("historico"));
                lista.add(his);
            }
        } catch (SQLException ex) {
        }
        return lista;
    }

//    public List<Historico> ListarHistoricosMov() {
//
//        List<Historico> lista = new ArrayList<Historico>();
//
//        try {
//            String sql = "select historico from historico order by cod_historico desc";
//            PreparedStatement stmt = con.prepareStatement(sql);
//            ResultSet rs = stmt.executeQuery();
//
//            while (rs.next()) {
//                Historico his = new Historico();
//                his.setHistorico(rs.getString("historico"));
//                lista.add(his);
//            }
//        } catch (SQLException ex) {
//        }
//        return lista;
//    }

    public List<Historico> ProcurarHistoricoLetra(String hist) {

        List<Historico> lista = new ArrayList<Historico>();

        try {
            String sql = "select * from historico where historico like ?";
            PreparedStatement stmt = con.prepareStatement(sql);
            stmt.setString(1, "%" + hist + "%");
            ResultSet rs = stmt.executeQuery();

            while (rs.next()) {
                Historico h = new Historico();
                h.setCod_historico(rs.getInt("cod_historico"));
                h.setHistorico(rs.getString("historico"));
                lista.add(h);
            }
        } catch (SQLException ex) {
        }
        return lista;
    }
    
      public List<String> ListarHistoricosMov() {

        List<String> lista = new ArrayList<String>();

        try {
            String sql = "select historico from historico";
            PreparedStatement stmt = con.prepareStatement(sql);
            ResultSet rs = stmt.executeQuery();

            while (rs.next()) {
           
               
                lista.add(rs.getString("historico"));    
            }
        } catch (SQLException ex) {
        }
        return lista;
    }
  

    public int UltimoCodHistorico() {

        int cod = 0;

        try {
            String sql = "select max(cod_historico) + 1 as codigo from historico";
            PreparedStatement ps = con.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();

            while (rs.next()) {
                cod = rs.getInt("codigo");
            }
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
        return cod;
    }

    public List<Historico> BuscarLimite(int num) {

        List<Historico> lista = new ArrayList<Historico>();

        try {
            String sql = "select * from historico limit ?, 10";
            PreparedStatement stmt = con.prepareStatement(sql);
            stmt.setInt(1, num);
            ResultSet rs = stmt.executeQuery();

            while (rs.next()) {
                Historico h = new Historico();
                h.setCod_historico(rs.getInt("cod_historico"));
                h.setHistorico(rs.getString("historico"));
                lista.add(h);
            }
        } catch (SQLException ex) {
        }
        return lista;
    }
}
